package com.ssfk.service.impl;


import com.ssfk.framework.service.JSONRequest;
import com.ssfk.framework.service.JSONResponse;
import com.ssfk.framework.utils.KeyConst;
import com.ssfk.service.errors.GlobalErr;
import com.ssfk.service.mybatis.auto.Statement;
import com.ssfk.service.mybatis.auto.StatementHistory;
import com.ssfk.service.mybatis.mapper.StatementMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by yuanjie on 2016/8/17.
 */
public class StatementEngine implements KeyConst {
    private static final Logger logger = LoggerFactory.getLogger(StatementEngine.class);
    private static final String KEY_STATEMENT = "statement";
    private static final String KEY_DRAW = "draw";
    public final static String DOT = ",";
    public final static String EQU = "=";
    public final static String QUO = "'";
    public final static String EMP = "";
    public final static String BLA = " ";

    private StatementMapper statementMapper;

    protected interface CreateFieldType {
        Integer UPDATE = 1;
        Integer INSERT = 2;
    }

    // 输出类型: 1 - Numberic, 2 - Map, 3 - List
    protected interface OutputClazz {
        Integer INT = 1;
        Integer MAP = 2;
        Integer LIST = 3;
    }

    public StatementEngine(StatementMapper mapper) {
        this.statementMapper = mapper;
    }

    public Statement applyPlaceholders(Statement statement, JSONRequest data) throws StatementException {
        Map<String, Placeholder> placeholders = Placeholder.createPlaceholders(statement.getSql(), true);
        return applyPlaceholders(placeholders, statement, data);

    }

    /**
     * @param statement
     * @param data
     * @return
     * @Title : applyPlaceholders
     */
    public Statement applyPlaceholders(Map<String, Placeholder> placeholders, Statement statement, JSONRequest data) throws StatementException {

        // 替换sql中的变量
        String sql = statement.getSql();
        for (String name : placeholders.keySet()) {
            Placeholder placeholder = placeholders.get(name);
            if (placeholder.isRequired() && !data.containsKey(name)) {
                logger.error("No variable named:" + name);
                throw new StatementException(GlobalErr.VARIABLE_NO_FOUND, "No variable named:" + name);
            }

            // 单引号处理
            String value = data.get(name) + EMP;
            if (value.contains("\\")) {
                value = value.replaceAll("\\\\", "\\\\\\\\");
            }

            if (value.contains("'")) {
                value = value.replaceAll("'", "\\\\'");
            }

            if (placeholder.isVarchar()) {
                value = QUO + value + QUO;
            } else {
                if (value.isEmpty()) {
                    logger.error("Numberic param can not be empty:" + name);
                    throw new StatementException(GlobalErr.VARIABLE_EMPTY, "Numberic param can not be empty:" + name);
                }
            }

            //while (sql.contains(placeholder.getPlaceholder())) {
            //    sql = sql.replace(placeholder.getPlaceholder(), value);
            //}

            // begin modify by yuanjie on 20161018
            // 解决replaceAll目标字符串中特殊字符的bug
            String replaceString = Pattern.quote(placeholder.getPlaceholder());
            sql = sql.replaceAll(replaceString, Matcher.quoteReplacement(value));
            // end modify by yuanjie on 20161018
        }
        statement.setSql(sql);
        logger.info("Apply placeholders finish:" + sql);
        return statement;
    }

    /**
     * @param fieldValue
     * @return boolean 返回类型
     * @throws
     * @Title : isCompleteField
     * @Description : 判断是否完整的字段
     * @params
     */
    protected boolean isCompleteField(String fieldValue) {
        return (!(fieldValue.indexOf("(") >= 0 && fieldValue.indexOf(")") < 0)
                && !(fieldValue.indexOf("${") >= 0 && fieldValue.indexOf("}") < 0));
    }

    /**
     * @param fieldsConfig
     * @return List<String> 返回类型
     * @throws
     * @Title : resolveFieldsConfig
     * @Description :
     * @params
     */
    public List<String> resolveFieldsConfig(String fieldsConfig) {
        List<String> fieldSplices = new ArrayList<>();
        int dotIndex = 0;
        do {
            dotIndex = fieldsConfig.indexOf(DOT);
            String fieldExpr = fieldsConfig;
            if (dotIndex > 0) {
                fieldExpr = fieldsConfig.substring(0, dotIndex);
            }
            do {
                // 字段值中包括函数未结束
                if (!isCompleteField(fieldExpr)) {
                    dotIndex = fieldsConfig.indexOf(DOT, dotIndex + 1);
                    if (dotIndex > 0) {
                        fieldExpr = fieldsConfig.substring(0, dotIndex);
                    } else {
                        fieldExpr = fieldsConfig;
                    }
                }
            } while (!isCompleteField(fieldExpr));

            if (null != fieldExpr && !fieldExpr.isEmpty()) {
                fieldSplices.add(fieldExpr);
            }
            fieldsConfig = fieldsConfig.substring(dotIndex + 1).trim();
        } while (dotIndex >= 0);

        return fieldSplices;
    }

    protected boolean createTableFields(List<TableField> tableFields, Map<String, Placeholder> placeholders, String fieldsExpr, String valuesExpr) {

        List<String> fieldSplices = resolveFieldsConfig(fieldsExpr);
        List<String> valueSplices = resolveFieldsConfig(valuesExpr);
        for (int i = 0; i < fieldSplices.size(); i++) {
            String fieldExpr = fieldSplices.get(i);
            String valueExpr = valueSplices.get(i);
            TableField field = createTableField(fieldExpr, CreateFieldType.INSERT);
            field.createValue(valueExpr);
            // 将字段添加列表中
            tableFields.add(field);
            placeholders.putAll(field.getPlaceholders());
        }

        if (tableFields.size() != valueSplices.size()) {
            logger.info("Fields and values no match?"
                    + tableFields.size() + ":" + valueSplices.size());
            return false;
        }
        return true;
    }

    /**
     * @param fieldsExpr
     * @return List<TableField> 返回类型
     * @throws
     * @Title : createTableFields
     * @Description :
     * @params
     */
    public List<TableField> createTableFields(List<TableField> updateFields, String fieldsExpr) {

        int dotIndex = 0;
        do {
            dotIndex = fieldsExpr.indexOf(DOT);
            String fieldExpr = fieldsExpr;
            if (dotIndex > 0) {
                fieldExpr = fieldsExpr.substring(0, dotIndex);
            }

            do {
                // 字段值中包括函数未结束
                if (!isCompleteField(fieldExpr)) {
                    dotIndex = fieldsExpr.indexOf(DOT, dotIndex + 1);
                    if (dotIndex > 0) {
                        fieldExpr = fieldsExpr.substring(0, dotIndex);
                    } else {
                        fieldExpr = fieldsExpr;
                    }
                }
            } while (!isCompleteField(fieldExpr));

            if (null != fieldExpr && !fieldExpr.isEmpty()) {
                updateFields.add(createTableField(fieldExpr.trim(), CreateFieldType.UPDATE));
            }
            fieldsExpr = fieldsExpr.substring(dotIndex + 1).trim();
        } while (dotIndex >= 0);

        return updateFields;
    }


    /**
     * @return Field 返回类型
     * @throws
     * @Title : createTableField
     * @Description : 创建表字段
     * @params @param
     * fieldExpr
     */
    public TableField createTableField(String fieldExpr, int createType) {
        TableField field = new TableField();
        if (createType == CreateFieldType.UPDATE) {
            int findIndex = fieldExpr.indexOf(EQU);
            if (findIndex > 0) {
                String filedName = fieldExpr.substring(0, findIndex);
                field.setName(filedName);
                field.setPlaceholders(Placeholder.createPlaceholders(filedName, false));
                field.createValue(fieldExpr.substring(findIndex + 1));
            }
        } else if (createType == CreateFieldType.INSERT) {
            field.setName(fieldExpr);
            field.setPlaceholders(Placeholder.createPlaceholders(fieldExpr, false));
        }
        return field;
    }

    /**
     * @param statement
     * @param data
     * @return
     * @Title : applyUpdateFields
     */
    public Statement applyUpdateFields(Statement statement, JSONRequest data) throws StatementException {

        String sql = statement.getSql().replaceAll("(?i)set", "set").replaceAll("(?i)where", "where");
        int begin = sql.indexOf("set ");
        if (begin < 0) {
            sql.indexOf("set\n");
        }
        int end = sql.indexOf(" where");
        if (end < 0) {
            end = sql.indexOf("\nwhere");
        }

        // 更新语句换行问题
        if (begin < 0 || end < 0) {
            throw new StatementException(GlobalErr.STATEMENT_INVALID, "Invalid sql" + sql);
        }

        List<TableField> updateFields = new ArrayList<>();
        createTableFields(updateFields, sql.substring(begin + 3, end));

        // 字段名的表达式
        StringBuilder builder = new StringBuilder(4096);
        builder.append(sql.substring(0, begin + 4));

        boolean haveFields = false;
        for (TableField field : updateFields) {
            // 判断是否需要进行替换的变量字段
            if (field.isReplaced()) {
                String fieldValue = field.getValue();
                String fieldName = field.getName();

                // 如果字段是必须的，但不存在该变量
                boolean fieldValid = true;
                for (String name : field.getPlaceholders().keySet()) {
                    if (!data.containsKey(name)) {
                        fieldValid = false;
                    }
                }

                if (fieldValid) {
                    builder.append(fieldName).append(EQU).append(fieldValue)
                            .append(DOT);
                    haveFields = true;
                } else {
                    if (field.isRequired()) {
                        logger.error("No variable replace:" + fieldValue);
                        throw new StatementException(GlobalErr.VARIABLE_NO_FOUND, "No variable replace:" + fieldValue);
                    }
                }
            } else {
                haveFields = true;
                builder.append(field.getName()).append(EQU).append(field.getValue())
                        .append(DOT);
            }
        }

        // 去掉变量表达式中的第一个DOT，因为表达式已经结束
        if (haveFields) {
            builder.deleteCharAt(builder.length() - 1);
        }

        // 添加条件表达式
        builder.append(sql.substring(end));
        statement.setSql(builder.toString());

        applyPlaceholders(statement, data);

        return statement;
    }

    protected Statement resolveStatement(JSONRequest data) throws StatementException {
        String name = data.getString(KEY_STATEMENT);
        Statement statement = statementMapper.getStatement(name);
        if (null == statement) {
            throw new StatementException(GlobalErr.STATEMENT_NO_FOUND, "No statment named:" + name);
        }

        String tableName = "";
        // 通过语句判断执行类型
        String sql = statement.getSql().trim().toLowerCase();

        // FIXME : 这里没有取出select table值，如果有多个表名只能取出第一个
        Pattern pattern = Pattern.compile("(?i)(update|insert +into) +(\\w+) +.*");
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            tableName = matcher.group(2);
        }
        statement.setTable_name(tableName);

        if (sql.startsWith("select")) {
            statement.setTable_op(TableOp.SELECT);
            applyPlaceholders(statement, data);
        } else if (sql.startsWith("update")) {
            statement.setTable_op(TableOp.UPDATE);
            applyUpdateFields(statement, data);
        } else if (sql.startsWith("insert")) {
            statement.setTable_op(TableOp.INSERT);
            applyInsertFields(statement, data);
        } else if (sql.startsWith("call")) {
            statement.setTable_op(TableOp.CALLBALE);
            applyPlaceholders(statement, data);
        } else if (sql.startsWith("delete")) {
            statement.setTable_op(TableOp.DELETE);
            applyPlaceholders(statement, data);
        } else {
            throw new StatementException(GlobalErr.STATEMENT_NO_FOUND, "Invalid statement:" + name);
        }

        logger.info("Resolve sql:" + statement.getSql());
        return statement;
    }

    protected Statement applyInsertFields(Statement statement, JSONRequest data) throws StatementException {

        List<TableField> tableFields = new ArrayList<>();
        Map<String, Placeholder> placeholders = new HashMap<>();
        Matcher matcher = Pattern.compile("(?i)(insert[\n ]+into[\n ]*\\w+[\n ]*\\()(.*)\\)[\n ]*values[\n ]*\\((.*)\\)([\n ]*on[\n ]+duplicate[\n ]+key[\n ]+update.*)").matcher(statement.getSql());
        String prefix = "";
        String suffix = "";
        if (matcher.matches()) {
            createTableFields(tableFields, placeholders, matcher.group(2), matcher.group(3));
            prefix = matcher.group(1);
            suffix = matcher.group(4);
        } else {
            matcher = Pattern.compile("(?i)(insert[\n ]+into[\n ]*\\w+[\n ]*\\()(.*)\\)[\n ]*values[\n ]*\\((.*)\\)").matcher(statement.getSql());
            if (matcher.matches()) {
                createTableFields(tableFields, placeholders, matcher.group(2), matcher.group(3));
                prefix = matcher.group(1);
            }
        }


        // 开始组装sql
        StringBuilder fieldsBuilder = new StringBuilder(4096);
        fieldsBuilder.append(prefix);
        StringBuilder valuesBuilder = new StringBuilder(4096);
        valuesBuilder.append(")values(");

        boolean haveFields = false;
        for (TableField field : tableFields) {
            // 判断是否需要进行替换的变量字段
            if (field.isReplaced()) {
                String fieldValue = field.getValue();
                String fieldName = field.getName();
                // 如果字段是必须的，但不存在该变量
                boolean fieldValid = true;
                for (String name : field.getPlaceholders().keySet()) {
                    if (!data.containsKey(name)) {
                        fieldValid = false;
                    }
                }

                if (fieldValid) {
                    fieldsBuilder.append(fieldName).append(DOT);
                    valuesBuilder.append(fieldValue).append(DOT);
                    haveFields = true;
                } else {
                    if (field.isRequired()) {
                        logger.error("No variable replace :" + fieldValue);
                        throw new StatementException(GlobalErr.VARIABLE_NO_FOUND, "No variable replace:" + fieldValue);
                    }
                }
            } else {
                fieldsBuilder.append(field.getName()).append(DOT);
                valuesBuilder.append(field.getValue()).append(DOT);
                haveFields = true;
            }
        }

        // 去掉最后结束的DOT
        if (haveFields) {
            fieldsBuilder = fieldsBuilder.deleteCharAt(fieldsBuilder.length() - 1);
            valuesBuilder = valuesBuilder.deleteCharAt(valuesBuilder.length() - 1);
        }

        fieldsBuilder.append(valuesBuilder).append(")").append(suffix);
        statement.setSql(fieldsBuilder.toString());

        Map<String, Placeholder> placeholders1 = Placeholder.createPlaceholders(statement.getSql(), true);
        placeholders1.putAll(placeholders);

        applyPlaceholders(placeholders1, statement, data);

        return statement;
    }


    public JSONResponse execute(JSONRequest data) {
        JSONResponse res = new JSONResponse();
        try {
            // 处理statement参数
            Statement statement = resolveStatement(data);
            if (TableOp.SELECT.equals(statement.getTable_op())) {
                return handleSelect(statement, data);
            } else if (TableOp.UPDATE.equals(statement.getTable_op())) {
                return handleUpdate(statement, data);
            } else if (TableOp.INSERT.equals(statement.getTable_op())) {
                return handleInsert(statement, data);
            } else if (TableOp.CALLBALE.equals(statement.getTable_op())) {
                return handleCallable(statement, data);
            } else if (TableOp.DELETE.equals(statement.getTable_op())) {
                return handleDelete(statement, data);
            }
        } catch (StatementException e) {
            res.error(e.getStatus(), e.getMessage());
        }

        return res;
    }

    protected void addHistory(Statement statement, JSONRequest data, JSONResponse result) {
        StatementHistory history = data.toJavaObject(StatementHistory.class);
        history.setTable_name(statement.getTable_name());
        history.setSql(statement.getSql());
        history.setMessage(result.toJSONString());
        history.setCreate_time(new Date());
        statementMapper.addHistory(history);
    }

    public JSONResponse handleUpdate(Statement statement, JSONRequest data) {
        JSONResponse handleResult = new JSONResponse();
        String sql = statement.getSql();
        handleResult.put(KEY_SIZE, statementMapper.update(sql));
        addHistory(statement, data, handleResult);
        return handleResult;
    }

    public JSONResponse handleInsert(Statement statement, JSONRequest data) {
        JSONResponse handleResult = new JSONResponse();
        handleResult.put(KEY_SIZE, statementMapper.update(statement.getSql()));
        addHistory(statement, data, handleResult);
        return handleResult;
    }

    public JSONResponse handleCallable(Statement statement, JSONRequest data) {
        JSONResponse handleResult = new JSONResponse();
        handleResult.put(KEY_SIZE, statementMapper.callable(statement.getSql()));
        addHistory(statement, data, handleResult);
        return handleResult;
    }

    public JSONResponse handleDelete(Statement statement, JSONRequest data) {
        JSONResponse handleResult = new JSONResponse();
        handleResult.put(KEY_SIZE, statementMapper.delete(statement.getSql()));
        addHistory(statement, data, handleResult);
        return handleResult;
    }

    public JSONResponse handleSelect(Statement statement, JSONRequest data) {

        JSONResponse handleResult = new JSONResponse();
        // 分页参数
        Integer iDisplayLength = data.getInteger("iDisplayLength");
        Integer iDisplayStart = data.getInteger("iDisplayStart");

        // 搜索关键字
        String sSearch = data.getString("sSearch");

        // 计数语句
        String countSql = "Select count(*) from (" + statement.getSql() + ") a";
        String recordsSql = statement.getSql();

        // 搜索处理
        if (null != sSearch && !sSearch.isEmpty()) {
            String likeFieldsString = statement.getLike_fields();
            if (null != likeFieldsString && !likeFieldsString.isEmpty()) {
                recordsSql = "Select * from (" + statement.getSql() + ") a where " + likeFieldsString + " like '%" + sSearch
                        + "%'";

                String filteredSql = countSql + " where " + likeFieldsString + " like '%" + sSearch + "%'";
                handleResult.setRecordsFiltered(statementMapper.count(filteredSql));
            }
        } else {
            recordsSql = "Select * from (" + statement.getSql() + ") a";
        }

        if (null != iDisplayLength && null != iDisplayStart) {
            // 分页处理
            recordsSql += " limit " + iDisplayStart + "," + iDisplayLength;
        }

        // 统计查询数量
        handleResult.setRecordsTotal(statementMapper.count(countSql));
        if (null == handleResult.getRecordsFiltered()) {
            handleResult.setRecordsFiltered(handleResult.getRecordsTotal());
        }

        // 进行查询
        handleResult.put(statement.getOutput_name(), statementMapper.selectMuti(recordsSql));
        handleResult.setDraw(data.getString(KEY_DRAW));

        return handleResult;
    }
}
